﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using prototype123.models;
using prototype123.controllers;

namespace prototype123.data_access
{
    class TermItemDAO
    {
        private SqlConnection db_connection;

        public void Connect()
        {
            db_connection = new SqlConnection(SystemController.connectString);
            db_connection.Open();
        }

        public void Disconnect()
        {
            db_connection.Close();
        }

        //If the student chooses to use the HEI curriculum, this is called. It copies the official 
        //assignments and categories over to the students assignment/category tables. 
        public void AddCurriculum(int termID)
        {
            SqlCommand AddAssignments = new SqlCommand("INSERT INTO ASSIGNMENT(CAT_ID, TITLE, PTS_POSSIBLE) SELECT" +
                " CAT_ID, TITLE, PTS_POSSIBLE FROM PKG_ASMT WHERE CAT_ID = " + termID, db_connection);

            SqlCommand AddCategories = new SqlCommand("INSERT INTO ASMT_CAT(COURSE_ID, CAT_NAME, CAT_PERCENT, ITEM_ID) "
                + "SELECT COURSE_ID, CAT_NAME, CAT_PERCENT, " + termID + " FROM PKG_ASMT_CAT WHERE CAT_ID = " + termID, db_connection);

            AddAssignments.ExecuteNonQuery();
            AddCategories.ExecuteNonQuery();
        }

        //This registers a student in a class by creating appropriate entries in the db. if the student 
        //has decided to use the official HEI curriculum, it also calls AddCurriculum to handle that
        public void AddCourse(Student std, TermItem termitem, int courseID, int termID)
        {
            SqlCommand add_course = new SqlCommand("INSERT INTO TERM_ITEM(STU_ID, TERM_ID, COURSE_ID," +
                "TEXTBOOK, USE_HEI_CURR, TEACHER_TYPE) Values(" + std.StudentID + "," + termID + "," + courseID + ", '" +
                termitem.Textbook + "'," + termitem.UsingHEICurriculum + ", '" + Convert.ToChar(termitem.TeacherType) + "')", db_connection);

            add_course.ExecuteNonQuery();

            if (Convert.ToBoolean(termitem.UsingHEICurriculum)) { AddCurriculum(termID); }
        }

        //If the student has previously dropped this course and is now adding it, 
        //this is called and clears the DROPPED flag in the DB
        public void ReAddCourse(int itemID)
        {
            SqlCommand re_add_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 0 WHERE " +
                "ITEM_ID = " + itemID, db_connection);
            re_add_course.ExecuteNonQuery();
        }

        //if the student wants to drop the class, this is called and
        // the DROPPED flag is turned on
        public void DropCourse(int itemID)
        {
            SqlCommand drop_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 1 WHERE " +
            "ITEM_ID = " + itemID, db_connection);

            drop_course.ExecuteNonQuery();
        }

        //this checks to see if a student is eligible to take a class. If the student has
        //taken it and gotten at least a 60% (D-), then he is not eligible to take the class. 
        //So this checks to see if a record exists where the student 1) took the course and
        //2) got a 60 or higher on that course. if there's a match, return true, else false
        public bool HasPassedCourse(int stuid, string courseid, int termid)
        {
            SqlCommand checkreg = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid + " and " +
                "COURSE_ID = '" + courseid + "' and TERM_ID = " + termid + " and FINAL_GRADE >= 60", db_connection);

            SqlDataReader reader = checkreg.ExecuteReader();
            return reader.Read();
        }

    }
}
